import pymysql


# 建立数据库连接
def Mysql_connecter():
    conn = pymysql.connect(host='122.200.92.134', user='root', password='Cosmo_6003', database='socketio',
                           port=53306)
    return conn


# 插叙t_class
def Class_insert(class_name, should_num, sick_num, leader_name, status, weather, meetid, place, insert_time):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "insert into t_class(class_name,should_num,sick_num,leader_name,status,weather,meetid, place, insert_time) values('%s','%s','%s','%s'," \
          "'%s','%s','%s','%s','%s'); "
    try:
        real_sql = sql % (
            class_name, should_num, sick_num, leader_name, status, weather, meetid,
            place, insert_time)
        cur.execute(real_sql)
        class_id = conn.insert_id()
        conn.commit()
        # conn.close()
        print("插入t_class成功,id:" + str(class_id))
        return class_id
    except Exception as e:
        print(e)
        print('插入t_class失败')
        conn.close()
        return 0


# 插入t_people
def People_insert(name, job, unit, group, insert_time, pic_url, people_code, site, id_card, meetName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "insert into t_people(name,job,unit,p_group,insert_time,pic_url,people_code,site,id_card,meetName) values(" \
          "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') "
    try:
        real_sql = sql % (name, job, unit, group, insert_time, pic_url, people_code, site, id_card, meetName)
        cur.execute(real_sql)
        people_id = conn.insert_id()
        conn.commit()
        print("插入t_prople成功,id:" + str(people_id))
        return people_id
    except Exception as e:
        print(e)
        print('插入t_people失败')
        cur.close()
        return 0


# 插入数据到关系表
def Relationship_insert(class_id, people_id):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "insert into r_class_people(class_id,people_id) values(%s,%s)"
    try:
        real_sql = sql % (class_id, people_id)
        cur.execute(real_sql)
        relationship_id = conn.insert_id()
        conn.commit()
        print('插入r_class_people成功,id:' + str(relationship_id))
        return relationship_id
    except Exception as e:
        print(e)
        print('插入r_class_people失败')
        conn.close()
        return 0


# 根据会议id查询会议信息
def Select_class(meetName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from t_class where meetid='%s';"
    try:
        real_sql = sql % (meetName)
        cur.execute(real_sql)
        t_class = cur.fetchall()
        print('t_class查询成功')
        return t_class
    except Exception as e:
        print(e)
        print('查询t_class失败')
        return 0


# 根据会议地点查询会议信息
def Select_class_index(place):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select * from t_class where place='%s' order by insert_time desc limit 1;"
    try:
        real_sql = sql % (place)
        cur.execute(real_sql)
        t_class = cur.fetchone()
        print('获取会议室' + place + '信息正常')
        return t_class
    except Exception as e:
        print('获取会议室' + place + '信息失败')
        print(e)
        return 0


# 计算参会人数
def Count_num(meetName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select count(*) from r_class_people where class_id='%s'"
    try:
        real_sql = sql % (meetName)
        cur.execute(real_sql)
        people_num = cur.fetchone()
        print('获取参会人数正常')
        return people_num
    except Exception as e:
        print('获取参会人数失败')
        print(e)
        return 0


# 查询参会人员组别和单位
def Select_group(id_card, meetName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "SELECT " \
          "t3.zbmc," \
          "(SELECT NAME FROM mxwi.org WHERE org_id = (SELECT t1.org_id FROM mxwi.users t1 WHERE guara_card = '%s')), " \
          "( SELECT t1.pic_url FROM mxwi.users t1 WHERE guara_card = '%s' ) " \
          "from " \
          "zhangjiakou.t_train_record t2," \
          "zhangjiakou.t_train_plan_fzlr t3," \
          "zhangjiakou.t_train_plan_rclr t4 " \
          "where " \
          "t2.id = t4.train_id " \
          "AND t4.id = t3.rc_id " \
          "AND t4.id = '%s' " \
          "AND FIND_IN_SET( ( SELECT t1.userid FROM mxwi.users t1 WHERE guara_card = '%s' ), t3.rymc_id )"

    try:
        real_sql = sql % (id_card,id_card, meetName, id_card)
        cur.execute(real_sql)
        groups = cur.fetchall()
        return groups
    except Exception as e:
        print('获取组别单位失败')
        print(e)
        return 'null'


# 插入数据到t_context
def insert_context(meetid, contexts):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "insert into t_context (meetid,contexts) values('%s','%s');"
    try:
        real_sql = sql % (meetid, contexts)
        cur.execute(real_sql)
        contest_id = conn.insert_id()
        conn.commit()
        print('t_context表插入成功:' + str(contest_id))
        return contest_id
    except Exception as e:
        print('t_context插入失败')
        print(e)
        return 0


# 从t_context查询数据
def Select_context(meetName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select contexts from t_context where meetid='%s' order by id asc;"
    try:
        real_sql = sql % (meetName)
        cur.execute(real_sql)
        context = cur.fetchall()
        return context
    except Exception as e:
        print(e)
        return 0


# 根据会议id获取参会人员信息
def Select_people_list(meetName):
    conn = Mysql_connecter()
    # 创建游标对象
    cur = conn.cursor()
    sql = "select name,replace(pic_url,char(10),'') as pic_url from t_people where meetname='%s' order by insert_time desc;"
    try:
        real_sql = sql % (meetName)
        cur.execute(real_sql)
        people_lists = cur.fetchall()
        return people_lists
    except Exception as e:
        print(e)
        return 0
